package com.kunbo.cn.service.impl.system;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import com.alibaba.fastjson.JSONArray;
import com.kunbo.cn.service.system.SelectInfoService;

@Service
public class SelectInfoImpl implements SelectInfoService{
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	
	
	/**
	 * 拿到下拉选择框json 解析，创建数据和表
	 * @param jsonStr
	 * @return boolean
	 * @throws Exception
	 * 
	 * {"type":"1","tableName":"数据库表名","title":"数据表描述文件","textField":"显示字段name","valueField":"显示value_id"}
	 */
	@Override
	public int doSelect(String jsonStr) throws Exception{
		System.out.println("jsonStr="+jsonStr);
		int i=-1;
		//拿到json 判断是否为空
		if(jsonStr!=null&&jsonStr!=""){
			
			JSONObject obj = new JSONObject(jsonStr);
			//String type = obj.getString("type"); 
			String tableName = obj.getString("tableName");
			String title = obj.getString("title");
			//String textField = obj.getString("textField");
			//String valueField = obj.getString("valueField");
			if(isExistTableName(tableName)){
				//存在这个表名
				System.out.println("存在这个表=="+tableName);
				//-2  表名已存在
				i=-2;
			}else{
				//不存在这个表名
				System.out.println("不不不存在这个表=="+tableName);
				//0是本地数据  ，1是从其他表中获取的数据 ，
				//if("0".equals(type)){
					createTableSelect(tableName,"label","pid");
					//System.out.println("0=="+type);
				//} 
				i=insertSelectTable(tableName, title);
			}
			return i;
		}
		return i;
	}
	
	
	
	
	/**
	 * 根据表名判断在table_manage_select表中 是否存在这个表名
	 * @param table_name
	 * @return
	 * @throws SQLException 
	 */
	public boolean isExistTableName(String table_name) throws SQLException {
		String sql = "select table_name from table_manage_select ";
	    Connection conn =null;
		PreparedStatement stmt =null;
		boolean ret= false ;
		try {
			conn = jdbcTemplate.getDataSource().getConnection();
			stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery(sql);
			System.out.println("sql = "+sql);
			ResultSetMetaData data = rs.getMetaData();
		    //ResultSetMetaData data = jdbcTemplate.getDataSource().getConnection().prepareStatement(sql).executeQuery(sql).getMetaData();
		    while(rs.next()) {
		    	for(int i=1;i<=data.getColumnCount();i++) {
		    		System.out.println(rs.getString(i)); 
		    		if(table_name.equals(rs.getString(i))){
		    			ret = true ;
		    		}
		    	}
		    }
//		    DataSourceUtils.releaseConnection(conn,jdbcTemplate.getDataSource());
//		    conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			stmt.close();
	    	conn.close();
	    }
		
		return ret;
	}
	
	
	
	/**
	 * 创建数据库下拉表
	 * @param table_name
	 * @return
	 */
	 public boolean createTableSelect(String table_name,String textField,String textField1) {
			
		 String sql = "create table "+table_name+"(id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,"+textField+" varchar(255)," + textField1 +" varchar(255))  ";
		 System.out.println(sql);
		try
		{
			jdbcTemplate.execute(sql);
			return true;
		}
		catch(org.springframework.dao.DataAccessException e)
		{
			return false;
		}
	 }
	 
	 
	 
	 public int  insertSelectTable(String tableName,String title) throws Exception {
			
		    int i=0; 
		   
		    String sql="insert into sys_tree_select_manage(table_name,title) "
		    		+ "values('"+tableName+"','"+title+"')"; 
		    System.out.println(sql);
		    Connection conn = null;
		    PreparedStatement  preStmt = null;
		    try {
		    	conn = jdbcTemplate.getDataSource().getConnection();
		    	preStmt =conn.prepareStatement(sql);
			    i=preStmt.executeUpdate();  
		    }catch(Exception e) {
		    	e.getMessage();
		    }finally {
		    	preStmt.close();
		    	conn.close();
		    }
		    //preStmt.close();
		    System.out.println("创建副表成功："+i);
		    return i;//返回影响的行数，1为执行成功  
		}




	@Override
	public String getTreeSelectTable(String tableName) {
		// TODO Auto-generated method stub
		String sql  = " select id,label as title,pid from " + tableName ;
		List<Map<String, Object>>  list = jdbcTemplate.queryForList(sql);
		System.out.println(list.toString());
		
		JSONArray json = JSONArray.parseArray(list.toString().replaceAll("=", ":"));
		
		return json.toJSONString();
	}
	
	
	
	

}
